USE ChinaDW;
GO
IF (SELECT COUNT(1) FROM tempdb.sys.tables WHERE [name] like '#DLAUNCH_PROJECT_OBJECT_NAME_CHECK%') = 0
BEGIN
CREATE TABLE #DLAUNCH_PROJECT_OBJECT_NAME_CHECK(
        ID INT IDENTITY,
        SCHEMAS varchar(10) not null,
        ObjectName VARCHAR(100) not null,
        ObjectType VARCHAR(10) not null,
        STATUSS TINYINT DEFAULT 0 null
    )
    TRUNCATE TABLE #DLAUNCH_PROJECT_OBJECT_NAME_CHECK;
    INSERT INTO #DLAUNCH_PROJECT_OBJECT_NAME_CHECK
        (SCHEMAS, ObjectName, ObjectType)
    VALUES('DW', 'DIM_DLAUNCH_USER_INFO', 'TABLE'),
        ('DW', 'FACT_DLAUNCH_VISIT_RECORD', 'TABLE'),
        ('DW', 'FACT_DLAUNCH_VISIT_SUMMARY', 'TABLE'),
        ('DW', 'DIM_DLAUNCH_LIST', 'TABLE'),
        ('DW', 'VW_T_DIM_DLAUNCH_USER_INFO', 'VIEW'),
        ('DW', 'VW_T_FACT_DBM_DLAUNCH_VISIT_SUMMARY', 'VIEW'),
        ('DW', 'SP_DIM_DLAUNCH_LIST', 'PROCEDURE'),
        ('DW', 'SP_DIM_DLAUNCH_USER_INFO', 'PROCEDURE'),
        ('DW', 'SP_FACT_DLAUNCH_VISIT_RECORD', 'PROCEDURE'),
        ('DW', 'SP_FACT_DLAUNCH_VISIT_SUMMARY', 'PROCEDURE'),
        ('ODS', 'SP_ADDORUPDATECOMMENT', 'PROCEDURE'),
        ('ODS', 'SP_DW_SP_LOG', 'PROCEDURE'),
        ('ODS','DW_SP_LOG','TABLE'),
        ('ODS','DW_SP_INFO','TABLE'),
        ('ODS', 'DBM_ACT_DLAUNCH_ACCESS_RECORD', 'TABLE'),
        ('ODS', 'DBM_ACT_DLAUNCH_ANSWER_RECORD', 'TABLE'),
        ('ODS', 'DBM_ACT_DLAUNCH_PAGE_RECORD', 'TABLE'),
        ('ODS', 'DBM_ACT_DLAUNCH_VIDEO_RECORD', 'TABLE'),
        ('ODS', 'EXCEL_DLAUNCH_RPM_MANUAL_INPUT', 'TABLE'),
        ('ODS', 'EXCEL_DLAUNCH_LIST', 'TABLE'),
        ('ODS', 'EXCEL_DLAUNCH_PAGE_CODE_FILTER', 'TABLE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_ANSWER_RECORD_INITIALIZE', 'PROCEDURE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_ACCESS_RECORD_INITIALIZE', 'PROCEDURE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_VIDEO_RECORD_INITIALIZE', 'PROCEDURE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_PAGE_RECORD_INITIALIZE', 'PROCEDURE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_ACCESS_RECORD', 'PROCEDURE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_ANSWER_RECORD', 'PROCEDURE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_PAGE_RECORD', 'PROCEDURE'),
        ('ODS', 'SP_DBM_ACT_DLAUNCH_VIDEO_RECORD', 'PROCEDURE'),
        ('STG', 'DBM_DLAUNCH_VISIT_RECORD', 'TABLE'),
        ('STG', 'VW_T_DBM_DLAUNCH_RECORD', 'VIEW'),
        ('STG', 'SP_DBM_DLAUNCH_VISIT_RECORD', 'PROCEDURE');
END
GO
DECLARE @i INT = 1;
DECLARE @total INT = (SELECT Count(1) FROM #DLAUNCH_PROJECT_OBJECT_NAME_CHECK) + 1;
DECLARE @objectName varchar(100);
DECLARE @objectType varchar(100);
WHILE @i < @total
BEGIN
    SET @objectName = (Select concat(SCHEMAS,'.',ObjectName) FROM #DLAUNCH_PROJECT_OBJECT_NAME_CHECK WHERE ID = @i);
    SET @objectType = (Select 
        CASE ObjectType WHEN 'TABLE' THEN 'U'
        WHEN 'VIEW' THEN 'V'
        WHEN 'PROCEDURE' THEN 'P'
        WHEN 'PROC' THEN 'P'
        END ObjectType
    FROM #DLAUNCH_PROJECT_OBJECT_NAME_CHECK WHERE ID = @i);

    IF OBJECT_ID (@objectName, @objectType) IS NOT NULL
    BEGIN
        UPDATE #DLAUNCH_PROJECT_OBJECT_NAME_CHECK SET STATUSS =1 WHERE ID = @i;
    END
    SET @i = @i + 1;
END
GO
SELECT * FROM #DLAUNCH_PROJECT_OBJECT_NAME_CHECK WHERE STATUSS = 0;
GO

